const sqlite3 = require("sqlite3").verbose();
const { resolve } = require("path");
let db;

function init(dbName = "invest_tree.db") {
    dbName = resolve(__dirname, "../../dataBase/", dbName);
    if (db) return;
    db = new sqlite3.Database(dbName);
}

module.exports = {
    /***
     *
     * @param tableName：表名
     * @param field：要插入数据的字段名，例如：["id", "name", "age"]
     * @param values：要插入的字段的值，例如：[1, "tom", 8]，值的顺序要与字段名的顺序一致
     * @param dbName：数据库名：可不传
     * @returns {Promise<unknown>} 返回 promise
     */
    insert(tableName, field = [], values = [], dbName) {
        return new Promise((resolve, reject) => {
            try {
                init(dbName);
                const add = db.prepare(
                    `INSERT OR REPLACE INTO ${tableName} (${field.join(",")}) VALUES (${Array.apply(null, {
                        length: values.length,
                    })
                        .map((item) => "?")
                        .join(",")})`
                );
                add.run(...values);
                add.finalize();
                resolve(); 
            } catch (err) {
                reject(err);
            }
        });
    },
    /***
     *
     * @param tableName：表名
     * @param id：要删除数据的id值
     * @param dbName：数据库名：可不传
     * @returns {Promise<unknown>}
     */
    delete(tableName, id, dbName) {
        return new Promise((resolve, reject) => {
            try {
                init(dbName);
                db.run(`DELETE FROM ${tableName} WHERE ID = ${id}`, function (err) {
                    if (err) {
                        reject(err);
                        return;
                    }
                    resolve();
                });
            } catch (err) {
                reject(err);
            }
        });
    },
    /***
     *
     * @param tableName：表名
     * @param updateParmas：要更新的内容，例如：{ key: value }
     * @param id：更新数据的id值
     * @param dbName：数据库名：可不传
     * @returns {Promise<unknown>}
     */
    update(tableName, updateParmas = {}, id, dbName) {
        return new Promise((resolve, reject) => {
            try {
                init(dbName);
                const params = Object.keys(updateParmas)
                    .map((key) => `${key} = '${updateParmas[key]}'`)
                    .join(", ");
                db.run(`UPDATE ${tableName} set ${params} WHERE ID = ${id}`, function (err) {
                    if (err) {
                        reject(err);
                        return;
                    }
                    resolve();
                });
            } catch (err) {
                reject(err);
            }
        });
    },
    /***
     *
     * @param tableName: 表名
     * @param field: 查询字段；可为空，空的查全部
     * @param dbName：数据库名字，可不传
     * @returns {Promise<unknown>} 查询的结果
     */
    select(tableName, field = [], dbName) {
        return new Promise((resolve, reject) => {
            try {
                init(dbName);
                const result = [];
                function deboundFn(fn, options = { timeout: 100 }) {
                    let timer;
                    return function () {
                        if (timer) clearTimeout(timer);
                        timer = setTimeout(fn, options.timeout);
                    };
                }
                const debound = deboundFn(() => {
                    resolve(result);
                });
                const params = field.length ? field.join(", ") : "*";
                db.each(`SELECT ${params} FROM ${tableName}`, function (err, row) {
                    result.push(row);
                    debound();
                });
            } catch (err) {
                reject(err);
            }
        });
    },
};
// insert into company (id, name, age) values (2, 'Johy', 6);
